﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;
using Think.Lenovo.Com.Models.Questionnaire;
namespace Think.Lenovo.Com.DataAccess.Questionnaire
{
    /// <summary>
    /// 问卷和问题的关系操作类
    /// </summary>
    public class QuestionnaireQuestionRelation
    {
        public LinkedList<QuestionnaireQuestionRelationModel> GetQuestionnaireQuestionRelations(int questionnaireId) 
        {
            String conn = ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select * From tbl_questionnaire_question_r Where questionnaireId=" + questionnaireId);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString());
            LinkedList<QuestionnaireQuestionRelationModel> list = new LinkedList<QuestionnaireQuestionRelationModel>();
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        QuestionnaireQuestionRelationModel model = new QuestionnaireQuestionRelationModel();
                        int i = 0;
                        Int32.TryParse(row["Id"].ToString(), out i);
                        model.Id = i;
                        Int32.TryParse(row["questionnaireId"].ToString(), out i);
                        model.QuestionnaireId = i;
                        Int32.TryParse(row["questionId"].ToString(), out i);
                        model.QuestionId = i;
                        list.AddLast(model);
                    }
                }
            }
            return list;
        }
        public QuestionnaireQuestionRelationModel GetQueestionnaireQuestionRelation(int questionnaireId, int questionId)
        {
            String conn = ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Select * From tbl_questionnaire_question_r Where questionnaireId=" + questionnaireId)
                .Append(" And questionId="+questionId);
            DataSet ds = MysqlHelper.Query(conn, sql.ToString());
            QuestionnaireQuestionRelationModel model = new QuestionnaireQuestionRelationModel();
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if(ds.Tables[0].Rows.Count>0)
                    {
                        int i = 0;
                        Int32.TryParse(ds.Tables[0].Rows[0]["Id"].ToString(), out i);
                        model.Id = i;
                        Int32.TryParse(ds.Tables[0].Rows[0]["questionnaireId"].ToString(), out i);
                        model.QuestionnaireId = i;
                        Int32.TryParse(ds.Tables[0].Rows[0]["questionId"].ToString(), out i);
                        model.QuestionId = i;
                    }
                }
            }
            return model;
        }
        public int Delete(int questionnaireId)
        {
            String conn = ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Delete From tbl_questionnaire_question_r Where questionnaireId=" + questionnaireId);
            int affected = MysqlHelper.Delete(conn, sql.ToString(), null);
            return affected;
        }
        public long Add(QuestionnaireQuestionRelationModel model)
        {
            String conn = ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Insert into tbl_questionnaire_question_r ")
                .Append("(QuestionnaireId, QuestionId, Seq) values ")
                .Append("(@QuestionnaireId,@QuestionId, @Seq)");
            MySqlParameter[] ps = new MySqlParameter[] { 
                    new MySqlParameter("@QuestionnaireId",MySqlDbType.Int32),
                    new MySqlParameter("@QusetionId",MySqlDbType.Int32),
                    new MySqlParameter("@Seq",MySqlDbType.Int32)
            };
            ps[0].Value = model.QuestionnaireId;
            ps[1].Value = model.QuestionId;
            ps[2].Value = model.Seq;
            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;
        }
        public long Add(int questionnaireId,int questionId,int seq)
        {
            String conn = ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(128);
            sql.Append("Insert into tbl_questionnaire_question_r ")
                .Append("(QuestionnaireId, QuestionId, Seq) values ")
                .Append("(@QuestionnaireId,@QuestionId, @Seq)");
            MySqlParameter[] ps = new MySqlParameter[] { 
                    new MySqlParameter("@QuestionnaireId",MySqlDbType.Int32),
                    new MySqlParameter("@QusetionId",MySqlDbType.Int32),
                    new MySqlParameter("@Seq",MySqlDbType.Int32)
            };
            ps[0].Value = questionnaireId;
            ps[1].Value = questionId;
            ps[2].Value = seq;
            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;
        }
    }
}
